﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SFast;

namespace TestConsole
{


    public class procedure
    {

        public enum File
        {
            ID,
            Path,
            Order,
            ParentID,
            Depth,
            Other
        }

        public String createInsert()
        {
            String tableName = "CommonClass";  //表名称
            String spName = "spCommonClassInsert"; //存储过程名称

            //除去必须字段之外的字段
            Dictionary<String, String> fileDic = new Dictionary<string, string>();
            fileDic.Add("CCName", "nvarchar(50)");
            fileDic.Add("CCEnName", "nvarchar(200)");
            fileDic.Add("CCType", "varchar(10)");


            #region 必填的字段，顺序不可变
            String other = fileDic.Keys._ToCSV(",");
            Dictionary<File, String> mustFile = new Dictionary<File, string>();
            mustFile.Add(File.ID, "CCID");
            mustFile.Add(File.Path, "CCPath");
            mustFile.Add(File.Order, "CCOrder");
            mustFile.Add(File.ParentID, "CCPID");
            mustFile.Add(File.Depth, "CCDepth");
            mustFile.Add(File.Other, other);

            #region 生成字段
            String sqlFile = "";
            String sqlFileValue = "";
            foreach (File key in mustFile.Keys)
            {
                sqlFile += mustFile[key] + ",";
            }
            sqlFile = sqlFile.TrimEnd(',');
            #endregion

            //参数  必须
            Dictionary<String, String> parDic = new Dictionary<string, string>();
            parDic.Add("@C_Parent_ID", "int");   //必填
            //下面要符合 mustFile 中key 为Other 值的顺序
            foreach (String key in fileDic.Keys)
            {
                parDic.Add("@"+key, fileDic[key]);
            }


            foreach (String key in parDic.Keys)
            {
                if (key != "@C_Parent_ID")
                {
                    sqlFileValue += key + ",";
                }
            }
            sqlFileValue = sqlFileValue.TrimEnd(',');
            //parDic.Add("@C_Intro", "nvarchar(2000)");            
            //parDic.Add("@C_Url", "nvarchar(200)");

            String parSql = "";
            foreach (String key in parDic.Keys)
            {
                parSql += key + " " + parDic[key] + ",";
            }
            parSql = parSql.TrimEnd(',');

            #endregion

            #region 存储过程
            String insertSql = @"USE [PurviewDB]
                            GO

                            /****** Object:  StoredProcedure [dbo].[" + spName + @"]    Script Date: " + DateTime.Now.ToString() + @" ******/
                            SET ANSI_NULLS ON
                            GO

                            SET QUOTED_IDENTIFIER ON
                            GO

                            CREATE PROCEDURE [dbo].[" + spName + @"]
                            (
	                            " + parSql + @"
                            )

                            AS

                            Declare @Err As int
                            Set @Err=0
                            Begin TRAN

                            --通过现有记录获取栏目ID
                            Declare @C_ID As int
                            Declare @C_Depth As INT

                            Select @C_ID = Max(" + mustFile[File.ID] + @") From " + tableName + @"
                            IF @C_ID Is Not Null
	                            Set @C_ID = @C_ID+1
                            ELSE
	                            Set @C_ID = 1
	
	
                            --判断是否是顶级栏目，设置其C_Path和C_Order
                            Declare @C_Path As nvarchar(1000)
                            Declare @C_Order As int
                            IF @C_Parent_ID = 0
                            Begin
	                            Set @C_Path =Ltrim(Str(@C_ID))
	                            Select @C_Order = Max(" + mustFile[File.Order] + @") From " + tableName + @"
	                            IF @C_Order Is Not Null
		                            Set @C_Order = @C_Order + 1
	                            Else --如果没有查询到记录，说明这是第一条记录
		                            Set @C_Order = 1
                            --深度
	                            Set @C_Depth = 1
                            END
                            Else
                            Begin
	                            --获取父节点的路径和深度
	                            Select @C_Path = " + mustFile[File.Path] + @" ,@C_Depth = " + mustFile[File.Depth] + @" From " + tableName + @" Where
	                            " + mustFile[File.ID] + @"=@C_Parent_ID
	                            IF @C_Path Is Null
	                            Begin
		                            Set @Err = 1
		                            Goto theEnd
	                            END
	
                            --获取同父节点下的最大序号
	                            Select @C_Order = Max(" + mustFile[File.Order] + @") From " + tableName + @" Where " + mustFile[File.Path] + @" like ''+@C_Path+'|%' or " + mustFile[File.ID] + @" = @C_Parent_ID
	                            IF @C_Order Is Not Null --如果序号存在，那么将该序号后的所有序号都加1
	                            Begin
		                            --更新当前要插入节点后所有节点的序号
		                            Update " + tableName + @" Set " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @" +1 Where " + mustFile[File.Order] + @" >@C_Order
		                            --同父节点下的最大序号加上1，构成自己的序号
		                            Set @C_Order = @C_Order + 1
	                            End
	                            Else
	                            Begin
		                            Set @Err=1
		                            Goto theEnd
	                            END

	                            --父节点的路径加上自己的ID号，构成自己的路径
	                            Set @C_Path = @C_Path + '|' + Ltrim(Str(@C_ID))
	                            --深度
	                            Set @C_Depth = @C_Depth+1
                            END

                            --执行添加 (添加新列时修改此处)
                            Insert Into " + tableName + @"(" + sqlFile + @")
                            Values(@C_ID,@C_Path,@C_Order,@C_Parent_ID,@C_Depth," + sqlFileValue + @")

                            IF @@Error<>0
                            Begin
	                            Set @Err=1
	                            Goto theEnd
                            End
	                            --更新当前记录之后的记录的ORDER
	                            --Update " + tableName + @" Set " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"+1 Where " + mustFile[File.Order] + @" > @C_Order
	                            theEnd:
                            IF @Err=0
                            Begin
	                            Commit Tran
	                            Return @C_ID
                            End
                            Else
                            Begin
	                            Rollback Tran
	                            Return 0
                            END

                            GO
                

";
            #endregion

            return insertSql;

        }

        public String CreateUpdate()
        {
            String tableName = "CommonClass";  //表名称
            String spName = "spCommonClassUpdate"; //存储过程名称

            //除去必须字段之外的字段(*)
            Dictionary<String, String> fileDic = new Dictionary<string, string>();
            fileDic.Add("CCName", "nvarchar(50)");
            fileDic.Add("CCEnName", "nvarchar(200)");
            fileDic.Add("CCType", "varchar(10)");



            #region 必填的字段，顺序不可变
            Dictionary<File, String> mustFile = new Dictionary<File, string>();
            mustFile.Add(File.ID, "CCID");
            mustFile.Add(File.Path, "CCPath");
            mustFile.Add(File.Order, "CCOrder");
            mustFile.Add(File.ParentID, "CCPID");
            mustFile.Add(File.Depth, "CCDepth");
            mustFile.Add(File.Other, fileDic.Keys._ToCSV(","));  //(*)

            #endregion


            Dictionary<String, String> parDic = new Dictionary<string, string>();
            parDic.Add("@C_ID", "int");   //必填
            parDic.Add("@C_Parent_ID", "int");   //必填
            parDic.Add("@C_Order", "int");   //必填
            //下面要符合 mustFile 中key 为Other 值的顺序(*)
            foreach (String key in fileDic.Keys)
            {
                parDic.Add("@" + key, fileDic[key]);
            }

            #region 参数的sql
            String parSql = "";
            foreach (String key in parDic.Keys)
            {
                parSql += key + "  " + parDic[key] + ",";
            }
            parSql = parSql.TrimEnd(',');
            #endregion

            #region 更新的sql
            String updateKeyValSql = "";
            foreach (String key in mustFile[File.Other].Split(','))
            {
                updateKeyValSql += key + " = @" + key + ",";
            }
            updateKeyValSql = updateKeyValSql.TrimEnd(',');
            #endregion

            #region 存储过程
            String updateSql = @"USE [PurviewDB]
                                GO

                                /****** Object:  StoredProcedure [dbo].[" + spName + @"]    Script Date: " + DateTime.Now + @" ******/
                                SET ANSI_NULLS ON
                                GO

                                SET QUOTED_IDENTIFIER ON
                                GO

                                ------------------更新
                                CREATE PROCEDURE [dbo].[" + spName + @"]
                                (
	                                " + parSql + @"
                                )
                                AS


                                Declare @Err As int
                                Set @Err=0
                                Begin Tran
	                                --获取修改前的：C_Parent_ID,C_Depth,C_Order,C_Path
	                                Declare @oC_Parent_ID As int
	                                Declare @oC_Depth As int
	                                Declare @oC_Order As int
	                                Declare @oC_Path As nvarchar(1000)
	                                DECLARE @oldC_Order AS INT  --原始排序位置,用于排序

	                                Select @oC_Parent_ID = " + mustFile[File.ParentID] + @", @oC_Depth = " + mustFile[File.Depth] + @",@oC_Order = " + mustFile[File.Order] + @", 
                                            @oC_Path = " + mustFile[File.Path] + @" From " + tableName + @" Where " + mustFile[File.ID] + @" = @C_ID
	                                SET @oldC_Order=@oC_Order
	                                IF @oC_Parent_ID Is Null
	                                Begin
		                                Set @Err = 1
		                                Goto theEnd
	                                End
	                                --如果父ID没有改变，则直接修改栏目名和栏目简介 (如果添加新列修改此处)
	                                IF @oC_Parent_ID = @C_Parent_ID
	                                Begin
		                                Update " + tableName + @" Set " + updateKeyValSql + @" Where " + mustFile[File.ID] + @" = @C_ID
		                                IF @@Error <> 0										  
		                                BEGIN
			                                Set @Err = 2
			                                Goto theEnd
		                                END
	                                END
	
	                                Declare @nC_Path As nvarchar(1000)
	                                Declare @nC_Depth As int
	                                Declare @nC_Order As int
	                                --获取当前节点的所有子节点[包括自身] 注：如果返回 “1” 说明是单节点
	                                Declare @theCount As int
	                                Select @theCount = Count(" + mustFile[File.ID] + @") From " + tableName + @" Where " + mustFile[File.ID] + @"=@C_ID or " + mustFile[File.Path] + @" like ''+@oC_Path+'|%'
	                                IF @theCount Is Null
	                                Begin
		                                Set @Err = 3
		                                Goto theEnd
	                                End
	                                IF @C_Parent_ID=0 --如果是设置为顶级节点，将节点设置为最后一个顶级节点
	                                Begin
		                                --Print '设置为顶级栏目'
		                                Set @nC_Path = Ltrim(Str(@C_ID))
		                                Set @nC_Depth =1
		                                Select @nC_Order = Max(" + mustFile[File.Order] + @") From " + tableName + @"
		                                IF @nC_Order Is NULL
		                                Begin
			                                Set @Err = 4
			                                Goto theEnd
		                                End
		                                Set @nC_Order = @nC_Order - @theCount + 1
		                                --更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序
		                                --Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的：C_Order'
		                                Update " + tableName + @" Set " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"-@theCount Where (" + mustFile[File.Order] + @" >@oC_Order) And (" + mustFile[File.Path] + @" Not like ''+@oC_Path+'|%')
		                                IF @@Error <> 0
		                                Begin
			                                Set @Err = 7
			                                Goto theEnd
		                                End
		                                --Print '更新本栏目的：C_Parent_ID,C_Path,C_Depth,C_Order,C_Name,C_Intro'  (添加新列时修改此处)
		                                Print 'Order : '+Ltrim(Str(@nC_Order))
		                                Update " + tableName + @" Set " + mustFile[File.ParentID] + @"=@C_Parent_ID," + mustFile[File.Path] + @" = @nC_Path," + mustFile[File.Depth] + @" = @nC_Depth,
                                                " + mustFile[File.Order] + @" = @nC_Order, " + updateKeyValSql + @" Where " + mustFile[File.ID] + @" = @C_ID
		                                IF @@Error <> 0
		                                Begin
			                                Set @Err = 5
			                                Goto theEnd
		                                End
		                                --Print '更新本栏目下的所有子栏目的：C_Path,C_Depth,C_Order'
		                                Update " + tableName + @" Set " + mustFile[File.Path] + @" = Replace(" + mustFile[File.Path] + @",@oC_Path,@nC_Path),
                                                " + mustFile[File.Depth] + @" = " + mustFile[File.Depth] + @" + (@nC_Depth-@oC_Depth),
                                                " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"+( @nC_Order-@oC_Order) Where " + mustFile[File.Path] + @" like ''+@oC_Path+'|%'
		                                IF @@Error <> 0
		                                Begin
			                                Set @Err = 6
			                                Goto theEnd
		                                End
	                                End
	                                Else
	                                Begin
		                                --获取未来父节点的相关信息,并设置本节点的相关值
		                                Select @nC_Depth = " + mustFile[File.Depth] + @",@nC_Path = " + mustFile[File.Path] + @" From " + tableName + @" Where " + mustFile[File.ID] + @" = @C_Parent_ID
		                                IF @nC_Depth Is NULL or @nC_Path Is Null
		                                Begin
			                                Set @Err = 8
			                                Goto theEnd
		                                End
		                                Set @nC_Depth = @nC_Depth +1
		                                Select @nC_Order =Max(" + mustFile[File.Order] + @") From " + tableName + @" Where " + mustFile[File.ID] + @" = @C_Parent_ID 
                                                or " + mustFile[File.Path] + @" like ''+@nC_Path+'|%'
		                                IF @nC_Order Is NULL
		                                Begin
			                                Set @Err = 9
			                                Goto theEnd
		                                End
		                                Set @nC_Path = @nC_Path +'|'+ Ltrim(Str(@C_ID))
		                                IF @nC_Order = @oC_Order+1 --如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变
		                                BEGIN
			                                --(当添加新列时,修改此处)
			                                Update " + tableName + @" Set " + mustFile[File.ParentID] + @"=@C_Parent_ID," + mustFile[File.Path] + @" = @nC_Path,
                                                    " + mustFile[File.Depth] + @" = @nC_Depth, " + updateKeyValSql + @" Where " + mustFile[File.ID] + @" = @C_ID
			                                IF @@Error <> 0
			                                Begin
				                                Set @Err = 10
				                                Goto theEnd
			                                End
		                                End
		                                Set @nC_Order = @nC_Order + 1
		                                --更新三部分 1 本树更改之前的后面（或前面）记录的顺序 1 节点本身 3 所有子节点
		                                --分为向上移或象下移
		                                --Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置] [不包括本栏目下的子栏目]的：C_Order'
		                                IF @nC_Order < @oC_Order
		                                Begin
			                                Update " + tableName + @" Set " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"+@theCount 
                                            Where " + mustFile[File.Order] + @"<@oC_Order And " + mustFile[File.Order] + @" >=@nC_Order 
                                                    And (" + mustFile[File.Path] + @" Not like ''+@oC_Path+'|%') And " + mustFile[File.ID] + @"<>@C_ID
			                                IF @@Error <> 0
			                                Begin
				                                Set @Err = 12
				                                Goto theEnd
			                                End
		                                End
		                                Else
		                                Begin
			                                Update " + tableName + @" Set " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"-@theCount 
                                            Where " + mustFile[File.Order] + @" >@oC_Order And " + mustFile[File.Order] + @"<@nC_Order And (" + mustFile[File.Path] + @" Not like ''+@oC_Path+'|%') And " + mustFile[File.ID] + @"<>@C_ID
			                                IF @@Error <> 0
			                                Begin
				                                Set @Err = 13
				                                Goto theEnd
			                                END
		                                End
		                                --Print '更新本栏目的：C_Parent_ID,C_Path,C_Depth,C_Order,C_Name,C_Intro'
		                                Print 'Order : '+Ltrim(Str(@nC_Order))
		                                IF @nC_Order > @oC_Order
		                                Set @nC_Order = @nC_Order - @theCount   --(当添加新列时,修改此处)
		                                Update " + tableName + @" Set " + mustFile[File.ParentID] + @"=@C_Parent_ID," + mustFile[File.Path] + @" = @nC_Path,
                                                " + mustFile[File.Depth] + @" = @nC_Depth," + mustFile[File.Order] + @" = @nC_Order, " + updateKeyValSql + @" Where " + mustFile[File.ID] + @" = @C_ID
		                                IF @@Error <> 0
		                                Begin
			                                Set @Err = 10
			                                Goto theEnd
		                                End
		                                --Print '更新本栏目下的所有子栏目的：Class_Paht,C_Depth,C_Order'   
		                                Update " + tableName + @" Set " + mustFile[File.Path] + @" = Replace(" + mustFile[File.Path] + @",@oC_Path,@nC_Path),
                                                " + mustFile[File.Depth] + @" = " + mustFile[File.Depth] + @" + (@nC_Depth-@oC_Depth)," + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"+(@nC_Order-@oC_Order) 
                                        Where " + mustFile[File.Path] + @" like ''+@oC_Path+'|%'
		                                IF @@Error <> 0
		                                Begin
			                                Set @Err = 11
			                                Goto theEnd
		                                End
	                                END
	
	                                -- =============================================
	                                -- Author:		<石姣男>
	                                -- Create date: <2012/3/19>
	                                -- Description:	<分类排序问题>
	                                -- =============================================
	                                --分类排序
	                                --获取修改前的：C_Parent_ID,C_Depth,C_Order,C_Path
                                    
                                    --旧父ID 和新父ID相同表示同级排序
	                                IF @C_Order <> 0 AND @oC_Parent_ID = @C_Parent_ID
	                                BEGIN
		                                DECLARE @NewOrder INT  --最终排序位置ID
		
		                                --做完上面动作后，当前修改项将会放到同级别的最低部
                                        --以下操作记录他当前的信息
		                                Select @oC_Parent_ID = " + mustFile[File.ParentID] + @", @oC_Depth = " + mustFile[File.Depth] + @",@oC_Order = " + mustFile[File.Order] + @", 
                                                    @oC_Path = " + mustFile[File.Path] + @" From " + tableName + @" Where " + mustFile[File.ID] + @" = @C_ID
		
		                                IF	@C_Order = @C_ID  --如果目标ID跟本身ID相等,说明白位置不变
		                                BEGIN
			                                SET  @NewOrder = @oldC_Order --给新排序位置赋值为原始排序位置
		                                END
		                                ELSE
		                                BEGIN
			                                --获取目标的排序位置
			                                SELECT @NewOrder = " + mustFile[File.Order] + @"  FROM " + tableName + @" WHERE  " + mustFile[File.ID] + @"=@C_Order
		                                END
		
		
		                                DECLARE @Poor INT  --记录差值
		
		                                IF @oC_Parent_ID Is Null
		                                Begin
			                                Set @Err = 1
			                                Goto theEnd
		                                END
		
		
		                                SET @Poor =@oC_Order-@NewOrder   --当前位置减去理想位置,计算中间差值
		                                UPDATE " + tableName + @" SET " + mustFile[File.Order] + @"=" + mustFile[File.Order] + @"-@Poor  
                                        WHERE '|'+" + mustFile[File.Path] + @"+'|' like '%|'+LTRIM(STR(@C_ID))+'|%' 
		                                UPDATE " + tableName + @" SET " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @"+@theCount 
                                        WHERE " + mustFile[File.Order] + @" <@oC_Order AND " + mustFile[File.Order] + @" >=@NewOrder AND '|'+" + mustFile[File.Path] + @"+'|' NOT like '%|'+LTRIM(STR(@C_ID))+'|%'
	                                END

	
	
	                                -- =============================================
	                                -- end
	                                -- =============================================
	
                                theEnd:
                                IF @Err<>0 --如果有错误则返回错误号
                                Begin
	                                Rollback Tran
	                                Return @Err
                                End
                                Else --如果没有错误就返回0
                                Begin
	                                Commit TRAN
	                                Return 0
                                End

                                GO

                                ";
            #endregion

            return updateSql;
        }

        public String createDelete()
        {
            String tableName = "CommonClass";  //表名称
            String spName = "spCommonClassDelete"; //存储过程名称

            #region 必填的字段，顺序不可变
            Dictionary<File, String> mustFile = new Dictionary<File, string>();
            mustFile.Add(File.ID, "CCID");
            mustFile.Add(File.Path, "CCPath");
            mustFile.Add(File.Order, "CCOrder");
            mustFile.Add(File.ParentID, "CCPID");
            mustFile.Add(File.Depth, "CCDepth");
            mustFile.Add(File.Other, "CCName,CCEnName");
            #endregion

            #region  存储过程
            String deleteSql = @"USE [PurviewDB]
                                GO

                                /****** Object:  StoredProcedure [dbo].[" + spName + @"]    Script Date: "+DateTime.Now+@" ******/
                                SET ANSI_NULLS ON
                                GO

                                SET QUOTED_IDENTIFIER ON
                                GO

                                CREATE PROCEDURE [dbo].[" + spName + @"]
                                (
	                                @C_ID int
                                )
                                AS

                                Declare @Err As int
                                Set @Err = 0
                                Begin Tran
                                --首先查询该节点下是否有子节点
	                                Select " + mustFile[File.ID] + @" From " + tableName + @" Where " + mustFile[File.ParentID] + @" = @C_ID
	                                IF @@RowCount<>0
	                                Begin
		                                Set @Err = 1
		                                Goto theEnd
	                                End
	                                --获取该节点的C_Order,为了删除后整理其他记录的顺序
	                                Declare @C_Order As int
	                                Select @C_Order = " + mustFile[File.Order] + @" From " + tableName + @" Where " + mustFile[File.ID] + @" = @C_ID
	                                IF @C_Order Is NUll
	                                Begin
		                                Set @Err =2
		                                Goto theEnd
	                                End
	                                --更新其他记录的C_Order
	                                Update " + tableName + @" Set " + mustFile[File.Order] + @" = " + mustFile[File.Order] + @" -1 Where " + mustFile[File.Order] + @" >@C_Order
	                                IF @@Error<>0
	                                Begin
		                                Set @Err =3
		                                Goto theEnd
	                                End
	                                --删除操作
	                                Delete From " + tableName + @" Where " + mustFile[File.ID] + @"=@C_ID
	                                IF @@Error<>0
	                                Begin
		                                Set @Err =4
		                                Goto theEnd
	                                End
	                                --更新其他记录的C_ID
	                                --Update " + tableName + @" Set " + mustFile[File.ID] + @"= " + mustFile[File.ID] + @" - 1 Where " + mustFile[File.ID] + @" >@C_ID
	                                --IF @@Error<>0
	                                -- Begin
	                                -- Set @Err =5
	                                -- Goto theEnd
	                                -- End
	                                --
                                theEnd:
                                IF @Err = 0
                                Begin
	                                Commit Tran
	                                Return 0 --删除成功
                                End
                                Else
                                Begin
	                                IF @Err=1
	                                Begin
			                                Rollback Tran
		                                Return 1 --有子节点
	                                END
	                                Else
	                                Begin
		                                Rollback Tran
		                                Return 2--未知错误
	                                End
                                End

                                GO
                    ";
            #endregion

            return deleteSql;

        }

    }
}
